package com.sys.service;

import com.sys.GlobalVariable;
import com.sys.entity.Customer;
import com.sys.entity.Trainer;
import com.sys.exception.DuplicateCardException;
import com.sys.utils.DbUtil4;
import com.sys.utils.DbUtil4Factory;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class TrainerService {
    public List<Map<String, Object>> showList(int uid, String tid, String tname) throws Exception {
        DbUtil4 db = null;
        List<Map<String, Object>> list = null;
        String sql = "SELECT tid,tname,tsex,tage,grade,tphone,tcourse,create_time,modify_time,isdel,uid FROM t_trainer where isdel = 0 and uid = ?";
        List paramList = new ArrayList();
        paramList.add(uid);
        if(tid!=null && !"".equals(tid)){
            sql += " and tid like ?";
            paramList.add(tid+"%");
        }
        if(tname!=null && !"".equals(tname) && !"-1".equals(tname)){
            sql += " and tname like ?";
            paramList.add(tname+"%");
        }
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql, paramList.toArray() );
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return list;
    }
//    tname,tsex,tage,grade,tphone,tcourse,uid
    public void add(String tname, String tsex,String tage,String grade,String tphone,String tcourse, Integer uid) throws Exception {
        DbUtil4 db = null;
        String selectSQL = "select count(1) as num from t_trainer where tname=? and tsex=?  and tage=? and uid =? ";
        String sql = "INSERT INTO t_trainer (tid,tname,tsex,tage,grade,tphone,tcourse,uid) VALUES (?,?,?,?,?,?,?,?)";
        String tidSql = "select max(ifnull(tid,0))+1 from t_trainer";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            List<Map<String, Object>> list = db.query(selectSQL, new Object[]{tname, tsex, tage,uid});
            // 由于查询的是 select count(1) ，所以一定有一个值
//            Map<String, Object> map = list.get(0);
//            Long num = (Long) map.get("num");
            // 将上面两行代码合成一行
            Long num = (Long)  list.get(0).get("num");
            if(num == 0){ // 没有重复的记录
                //查询最大tid+1
                List<Map<String, Object>> list2 = db.query(tidSql,new Object[]{});
                Map<String, Object> map = list2.get(0);

                Object maxTid = map.get("max(ifnull(tid,0))+1");

                db.update(sql, new Object[]{maxTid,tname,tsex,tage,grade,tphone,tcourse,uid});
            }else{ // 如果名片存在，抛出异常
                throw new DuplicateCardException("顾客已经存在！");
            }

        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
    public Trainer showById(String tid) throws Exception{
        List<Trainer> list = null;
        DbUtil4 db = null;
        boolean t = false;

        String sql = "SELECT tid,tname,tsex,tage,grade,tphone,tcourse FROM t_trainer WHERE tid=? and isdel = 0";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            list = db.query(sql, new Object[]{tid}, Trainer.class);
            if(list!=null && !list.isEmpty()){
                Trainer trainer = list.get(0);
                return trainer;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
        return null;
    }
    public int update(String tname, String tsex,String grade,String tage,String tphone,String tcourse, int tid) throws Exception {
        DbUtil4 db = null;
        String sql = "update t_trainer set tname = ?, tsex = ?,grade = ?, tage = ?,tphone = ?,tcourse = ? where tid = ?";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            int i = db.update(sql, new Object[]{tname,tsex,grade,tage,tphone,tcourse,tid});
            return i;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
    public int delete(int tid) throws Exception {
        DbUtil4 db = null;
        String sql = "UPDATE t_trainer SET isdel = 1 WHERE tid = ?";
        try {
            db = DbUtil4Factory.getDbUtil4(GlobalVariable.dbconfigPath, "utf-8");
            int j = db.update(sql, new Object[]{tid});
            return j;
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (db != null) {
                    db.close();
                }
            } catch (SQLException e) {
                throw e;
            }
        }
    }
}
